package com.batunite.livedatabinding.dao

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import com.batunite.livedatabinding.bean.LevelBean

/**
 * 数据库创建和版本管理的帮助类。
 */
class DatabaseHelper(context: Context) :
    SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        private const val DATABASE_NAME = "LevelDatabase"
        private const val DATABASE_VERSION = 1

        private const val TABLE_NAME = "levels"
        private const val COLUMN_ID = "id"
        private const val COLUMN_NAME = "name"
        private const val COLUMN_COMPLETED = "completed"
    }

    fun isDatabaseExists(context: Context, databaseName: String): Boolean {
        val dbFile = context.getDatabasePath(databaseName)
        return dbFile.exists()
    }

    /**
     * 创建数据库时会调用，我们需要在里面创建我们的数据表。
     * onCreate只会调用一次，如果数据库已经存在，那么打开数据库是不会回调onCreate的。
     */
    override fun onCreate(db: SQLiteDatabase) {
        val createTableQuery =
            "CREATE TABLE $TABLE_NAME ($COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT, $COLUMN_NAME TEXT, $COLUMN_COMPLETED INTEGER)"
        db.execSQL(createTableQuery)
    }

    /**
     * 升级数据库时会调用，我们可以在这里面做升级操作。
     */
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        val dropTableQuery = "DROP TABLE IF EXISTS $TABLE_NAME"
        db.execSQL(dropTableQuery)
        onCreate(db)
    }

    /**
     * 添加一个对象
     */
    fun insertLevel(level: LevelBean): Long {
        val values = ContentValues().apply {
            put(COLUMN_NAME, level.name)
            put(COLUMN_COMPLETED, if (level.completed) 1 else 0)
        }

        val db = writableDatabase
        val id = db.insert(TABLE_NAME, null, values)
        db.close()

        return id
    }

    /**
     * 更新一个对象
     */
    fun updateLevel(level: LevelBean) {
        val values = ContentValues().apply {
            put(COLUMN_COMPLETED, if (level.completed) 1 else 0)
        }

        val db = writableDatabase
        db.update(TABLE_NAME, values, "$COLUMN_NAME=?", arrayOf(level.name))
        db.close()
    }

    /**
     * 查询一个对象
     */
    fun queryLevelBean(id: Int): LevelBean? {
        val cursor = readableDatabase.query(
            TABLE_NAME,
            null,
            "id = ?",
            arrayOf(id.toString()),
            null,
            null,
            null
        )
        return cursor.use {
            if (it.moveToFirst()) {
                LevelBean(
                    it.getString(it.getColumnIndexOrThrow("name")),
                    1 == it.getInt(it.getColumnIndexOrThrow("completed"))
                )
            } else {
                null
            }
        }
    }

    fun getAllLevels(): ArrayList<LevelBean> {
        val levels = ArrayList<LevelBean>()

        val selectQuery = "SELECT * FROM $TABLE_NAME"
        val db = readableDatabase
        val cursor: Cursor? = db.rawQuery(selectQuery, null)

        cursor?.let {
            if (it.moveToFirst()) {
                do {
                    val name = it.getString(it.getColumnIndex(COLUMN_NAME))
                    val completed = it.getInt(it.getColumnIndex(COLUMN_COMPLETED)) == 1
                    val level = LevelBean(name, completed)
                    levels.add(level)
                } while (it.moveToNext())
            }
        }

        cursor?.close()
        db.close()

        return levels
    }

    fun deleteAllLevels() {
        val db = writableDatabase
        db.delete(TABLE_NAME, null, null)
        db.close()
    }
}
